﻿using KPIS.DBM;
using KPIS.GERP.ESS.APP.App_UI.Commons;
using KPIS.GERP.ESS.MODEL;
using System;
using System.Data;
using System.Linq;
using System.Web.UI.WebControls;

namespace KPIS.GERP.ESS.APP.App_UI.Forms
{
    public partial class ImportAsset : System.Web.UI.Page
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        Main main = new Main();

        protected void Page_Load(object sender, EventArgs e)
        {
            //dbManager.Open();
            //DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, "select * from sys_sc_users");
            //aaa.DataSource = dtb;
            //aaa.DataBind();
            //dbManager.Close();
        }

        protected void cmdOk_Click(object sender, EventArgs e)
        {
            DateTime date = new DateTime(2014, 9, 30);
            int Complete = 0;
            int Error = 0;
            string ErrorCode = "";
            DataTable dtb = GetDataTableFromExcel(true, 13);

            foreach (DataRow dr in dtb.Rows)
            {
                string Code = dr["รหัสทรัพย์สิน"].ToString().Trim();

                if (string.IsNullOrEmpty(Code))
                {
                    continue;
                }
                
                if (Code.StartsWith("1") || Code.StartsWith("2") || Code.StartsWith("3") || Code.StartsWith("4") || Code.StartsWith("5") || 
                    Code.StartsWith("6") || Code.StartsWith("7") || Code.StartsWith("8") || Code.StartsWith("9") || Code.StartsWith("0"))
                {
                    Code = Code.Replace("-", "");
                }

                DataTable dtbAsset = GetAsset(Code);

                if (dtbAsset == null || dtbAsset.Rows.Count == 0)
                {
                    dtbAsset = GetAsset("HAI-" + Code);
                }

                if (dtbAsset == null || dtbAsset.Rows.Count == 0)
                {
                    dtbAsset = GetAsset(Code, true);
                }

                if (dtbAsset.Rows.Count > 0)
                {
                    try
                    {
                        int AssetId = Convert.ToInt32(dtbAsset.Rows[0][0].ToString());
                        string DeprePercent = dr["อัตราค่าเสื่อม"].ToString().Replace("%", "").Trim();
                        string Remarks = dr["หมวดครุภัณฑ์"].ToString().Trim();
                        double dDeprePercent = main.ConvertNullDouble(DeprePercent) ?? 0;
                        double LifeYear = 100 / dDeprePercent;
                        double Price = main.ConvertNullDouble(dr["ราคาที่ซื้อ"]) ?? 0;
                        DateTime? AssetDate = main.ConvertNullDateTime(dr["วันเดือนปีที่ซื้อ"]);

                        if (!AssetDate.HasValue)
                        {
                            Error++;
                            ErrorCode += Code + " [DateError] | ";
                            continue;
                        }

                        UpdateAsset(AssetId, LifeYear, AssetDate.Value, Price, Remarks);

                        double Amount = main.ConvertNullDouble(dr["ค่าเสื่อมสะสม ณ 30 กย 57"]) ?? 0;
                        InsertDepreciation(AssetId, date, Amount);
                        Complete++;
                    }
                    catch (Exception ex)
                    {
                        Error++;
                        ErrorCode += Code + " [" + ex.Message + "] | ";
                    }
                }
                else
                {
                    Error++;
                    ErrorCode += Code + "[" + dr["รหัสทรัพย์สิน"].ToString().Trim() + "] | ";
                }
            }

            lblComplete.Text = "Completed : " + Complete;
            lblError.Text = "Error : " + Error + " | " + ErrorCode;
        }

        private DataTable GetDataTableFromExcel(bool hasHeader, int? column)
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = FileUpload1.PostedFile.InputStream)
                {
                    pck.Load(stream);
                }

                var ws = pck.Workbook.Worksheets.First();
                DataTable dtb = new DataTable();

                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    dtb.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column{0}", firstRowCell.Start.Column));
                }

                var startRow = hasHeader ? 2 : 1;

                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, column.HasValue ? column.Value : ws.Dimension.End.Column];
                    var row = dtb.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }

                    if (row[2].ToString().Trim() != "")
                    {
                        dtb.Rows.Add(row);
                    }
                }

                return dtb;
            }
        }

        private DataTable GetAsset(string Code, bool bIncludeDeleted = false)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@code", Code);

                string sql = "select a.*"
                    + " from IcmAsset a"
                    //+ " left join IcmMovementHistory his on a.AssetId = his.AssetId"
                    + " where 0 = 0"
                    //+ (bIncludeDeleted ? "" : " and a.RecordStatus not like @RecordStatus")
                    + " and a.SerialNumber = @code";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        private void UpdateAsset(int AssetId, double LifeYear, DateTime AssetDate, double Price, string Remark)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@AssetId", AssetId);
                dbManager.AddParameters(1, "@LifeYear", LifeYear);
                dbManager.AddParameters(2, "@AssetDate", AssetDate);
                dbManager.AddParameters(3, "@Price", Price);
                dbManager.AddParameters(4, "@Remark", Remark);

                string sql = "update IcmAsset"
                    + " set LifeYear = @LifeYear"
                    + ", AssetDate = @AssetDate"
                    + ", Price = @Price"
                    + ", Remark = @Remark"
                    + " where AssetId = @AssetId";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        private void InsertDepreciation(int AssetId, DateTime CalculatedDate, double DepAmount)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@AssetId", AssetId);
                dbManager.AddParameters(1, "@CalculatedDate", CalculatedDate);
                dbManager.AddParameters(2, "@DepAmount", DepAmount);

                string sql = "insert into IcmAssetDepreciation"
                    + " (AssetId, CalculatedDate, DepAmount, RecordStatus, CreatedBy)"
                    + " values (@AssetId, @CalculatedDate, @DepAmount, 'W', 1)";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}